/* $RCSFile: DBHelper.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/DBHelper.cs $
 * $log$
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.IO;
using System.Diagnostics;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.Diagnostics;
using AddonNice.Settings;


namespace AddonNice.Helpers
{
	/// <summary>
	/// Summary description for DBHelper
	/// </summary>
	public class DBHelper : DBDataStore
	{
		// Internal Error list	
        public ArrayList errors                =   new ArrayList();
	
		/// <summary>
		/// Execute script using transaction
		/// </summary>
		public ArrayList ExecuteScript(string scriptPath, bool useTransaction)
		{
			return ExecuteScript(scriptPath, Config.DefSqlConnection, useTransaction);
		}
		
        /// <summary>
		/// Execute script (no transaction)
		/// </summary>
		public ArrayList ExecuteScript(string scriptPath)
		{
			return ExecuteScript(scriptPath, Config.DefSqlConnection);
		}
		
        /// <summary>
		/// Execute script using transaction
		/// </summary>
		public ArrayList ExecuteScript(string scriptPath, SqlConnection  theConnection, bool useTransaction)
		{
			if (!useTransaction)
				return ExecuteScript(scriptPath, theConnection); //FIX: Must pass connection as well
			string strScript                            =   GetScript(scriptPath);
			if ( InstallTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript Executing Script : {0}",scriptPath ),InstallTraceSwitch.Sw.Info);
			// Subdivide script based on GO keyword
			string[] sqlCommands                        =   Regex.Split(strScript, "\\sGO\\s", RegexOptions.IgnoreCase);
			//Open connection
			theConnection.Open();
			//Wraps execution on a transaction 
			//so we know that the script runs or fails
			string transactionName                      =   " ADNCRM";
			SqlTransaction myTrans                      =   theConnection.BeginTransaction(IsolationLevel.RepeatableRead, transactionName);
            if ( InstallTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine("[V]DBHelper ExecuteScript Start Script Transaction.",InstallTraceSwitch.Sw.Info);
			//Cycles command and execute them
			for (int s = 0; s <= sqlCommands.GetUpperBound(0); s++)
			{
				string mySqlText                        =   sqlCommands[s].Trim();
				try 
				{
					if (mySqlText.Length > 0)
					{
						if ( InstallTraceSwitch.Sw.TraceVerbose )
                            Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript Executing: {0}",mySqlText.Replace("\n", " ")),InstallTraceSwitch.Sw.Info);
						// Must assign both transaction object and connection
						// to Command object for a pending local transaction
						using (SqlCommand  sqldbCommand =   new SqlCommand()) 
						{
							sqldbCommand.Connection     =   theConnection;
							sqldbCommand.CommandType    =   CommandType.Text;
							sqldbCommand.Transaction    =   myTrans;
							sqldbCommand.CommandText    =   mySqlText;
							sqldbCommand.CommandTimeout =   150;
							sqldbCommand.ExecuteNonQuery();
						}
					}
				} 
				catch(Exception ex) 
				{
					myTrans.Rollback();
    				if (theConnection.State == ConnectionState.Open)
	        			theConnection.Close();
				    errors.Add(string.Format("<p>{0}<br/>{1}</p>",mySqlText,ex));
					if ( InstallTraceSwitch.Sw.TraceWarning )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript ExecuteScript Failed: {0}, ex : {1}",mySqlText,ex),InstallTraceSwitch.Sw.Info);
		            int count                           =   0;
		            while(ex.InnerException != null && count < 100)
		            {
			            ex                              =   ex.InnerException;
					    errors.Add(string.Format("<p>{0}<br/>{1}</p>",mySqlText,ex));
			            count++;
		            }
					throw;
				}
            }
		// Succesfully applied this script
		myTrans.Commit();
		if (theConnection.State == ConnectionState.Open)
			theConnection.Close();
		if ( InstallTraceSwitch.Sw.TraceVerbose )
            Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript END Commit Script Transaction errors: {0}.",errors.Count),InstallTraceSwitch.Sw.Info);
		return errors;
		}

		/// <summary>
		/// Execute script (no transaction)
        /// Break on first error
		/// </summary>
		public ArrayList ExecuteScript(string scriptPath, SqlConnection  theConnection)
		{
			string strScript                            =   GetScript(scriptPath);
			if ( InstallTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript Executing Script : {0}",scriptPath),InstallTraceSwitch.Sw.Info);
			// Subdivide script based on GO keyword
			string[] sqlCommands                        =   Regex.Split(strScript, "\\sGO\\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase);

	    	//Open connection
    		theConnection.Open();
			//Cycles command and execute them
			for (int s = 0; s <= sqlCommands.GetUpperBound(0); s++)
			{
				string mySqlText                        =   sqlCommands[s].Trim();
				try 
				{
                    int len                             =   mySqlText.Length;
					if ( len > 0 )
					{
						if ( InstallTraceSwitch.Sw.TraceVerbose )
                            Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]DBHelper ExecuteScript Executing: [[{0}...]]",mySqlText.Substring(0,(len > 100) ? 100:len)),InstallTraceSwitch.Sw.Info);
						using (SqlCommand  sqldbCommand =   new SqlCommand()) 
						{
							sqldbCommand.Connection     =   theConnection;
							sqldbCommand.CommandType    =   CommandType.Text;
							sqldbCommand.CommandText    =   mySqlText;
							sqldbCommand.CommandTimeout =   150;
							sqldbCommand.ExecuteNonQuery();
						}
					}
				} 
				catch(Exception ex) 
				{
		            if (theConnection.State == ConnectionState.Open)
			            theConnection.Close();
				    errors.Add(string.Format("<p>{0}<br/>{1}</p>",mySqlText,ex));
					if ( InstallTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DBHelper ExecuteScript Executing Script Failed : {0}, ex : {1}",mySqlText, ex),InstallTraceSwitch.Sw.Info);
		            int count                           =   0;
		            while(ex.InnerException != null && count < 100)
		            {
			            ex                              =   ex.InnerException;
					    errors.Add(string.Format("<p>{0}<br/>{1}</p>",mySqlText,ex));
			            count++;
		            }
					// Rethrow exception
					throw;
				}
			}
		if (theConnection.State == ConnectionState.Open)
			theConnection.Close();
		return errors;
		}

        static public Int32 ExeSQL(string sql)
		{
			int returnValue             =   -1;

			SqlConnection theConnection =   Config.DefSqlConnection;
			SqlCommand sqlCommand       =   new SqlCommand(sql, theConnection);
			try
			{ 
				sqlCommand.Connection.Open();
				returnValue             =   sqlCommand.ExecuteNonQuery();
			}

			catch (Exception e)
			{
				if ( InstallTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DBHelper ExeSQL : {0}",e),InstallTraceSwitch.Sw.Info);
				throw;
			}

			finally
			{ 
				sqlCommand.Dispose();
                if ( theConnection.State != ConnectionState.Closed )
				    theConnection.Close();
				theConnection.Dispose();
			}
			return returnValue;
		}

		/// <summary>
		/// Get the script from a file
		/// </summary>
		private static string GetScript(string scriptPath)
		{
			string strScript                =   string.Empty;
            StreamReader objStreamReader    =   null;
			try
			{ 
			    objStreamReader             =   File.OpenText(scriptPath);
				strScript                   =   objStreamReader.ReadToEnd();
			}
			catch (Exception e)
			{
				if ( InstallTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]DBHelper GetScript ex: {0}",e),InstallTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{ 
				objStreamReader.Close();
			}
			return strScript + Environment.NewLine; //Append carriage for execute last command
		}
		
	}
}

